﻿//------------------------------------------------------------------------------
// 创建标识: Copyright (C) 2008 Socansoft.com 版权所有

// 创建描述: SocanCode代码生成器自动创建于 2008-8-13 17:43:17
//
// 功能描述: 
//
// 修改标识: 
// 修改描述: 
//------------------------------------------------------------------------------

using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using IDAL;
using DBUtility;
using System.Collections;
using System.Collections.Generic;

namespace SQLServerDAL
{
    /// <summary>
    /// 数据访问类 FUNCTIONS
    /// </summary>
    public class FUNCTIONS : IFUNCTIONS
    {
        public FUNCTIONS()
        { }

        #region  成员方法
        /// <summary>
        /// 增加一条数据

        /// </summary>
        public string Add(Model.FUNCTIONS model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [FUNCTIONS](");
            strSql.Append("[FUNC_CODE],[FUNC_NAME],[FUNC_URL],[FUNC_FATHERCODE],[FUNC_LEVEL],[FUNC_FIELD1],[FUNC_FIELD2])");
            strSql.Append(" values (");
            strSql.Append("@FUNC_CODE,@FUNC_NAME,@FUNC_URL,@FUNC_FATHERCODE,@FUNC_LEVEL,@FUNC_FIELD1,@FUNC_FIELD2)");
            SqlParameter[] parameters = {
                    new SqlParameter("@FUNC_CODE", SqlDbType.VarChar,15),
                    new SqlParameter("@FUNC_NAME", SqlDbType.VarChar,100),
                    new SqlParameter("@FUNC_URL", SqlDbType.VarChar,200),
                    new SqlParameter("@FUNC_FATHERCODE", SqlDbType.VarChar,15),
                    new SqlParameter("@FUNC_LEVEL", SqlDbType.Int),
                    new SqlParameter("@FUNC_FIELD1", SqlDbType.VarChar,20),
                    new SqlParameter("@FUNC_FIELD2", SqlDbType.VarChar,20)
            };
            parameters[0].Value = model.FUNC_CODE;
            parameters[1].Value = model.FUNC_NAME;

            if (model.FUNC_URL != null)
                parameters[2].Value = model.FUNC_URL;
            else
                parameters[2].Value = DBNull.Value;

            parameters[3].Value = model.FUNC_FATHERCODE;
            parameters[4].Value = model.FUNC_LEVEL;

            if (model.FUNC_FIELD1 != null)
                parameters[5].Value = model.FUNC_FIELD1;
            else
                parameters[5].Value = DBNull.Value;


            if (model.FUNC_FIELD2 != null)
                parameters[6].Value = model.FUNC_FIELD2;
            else
                parameters[6].Value = DBNull.Value;


            if (SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0)
                   return model.FUNC_CODE;
            else
                return null;
        }

        /// <summary>
        /// 更新一条数据

        /// </summary>
        public bool Update(Model.FUNCTIONS model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [FUNCTIONS] set ");
            strSql.Append("[FUNC_NAME]=@FUNC_NAME,");
            strSql.Append("[FUNC_URL]=@FUNC_URL,");
            strSql.Append("[FUNC_FATHERCODE]=@FUNC_FATHERCODE,");
            strSql.Append("[FUNC_LEVEL]=@FUNC_LEVEL,");
            strSql.Append("[FUNC_LEAF]=@FUNC_LEAF,");
            strSql.Append("[FUNC_STOP]=@FUNC_STOP,");
            strSql.Append("[FUNC_FIELD1]=@FUNC_FIELD1,");
            strSql.Append("[FUNC_FIELD2]=@FUNC_FIELD2");
            strSql.Append(" where [FUNC_CODE]=@FUNC_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@FUNC_CODE", SqlDbType.VarChar,15),
                    new SqlParameter("@FUNC_NAME", SqlDbType.VarChar,100),
                    new SqlParameter("@FUNC_URL", SqlDbType.VarChar,200),
                    new SqlParameter("@FUNC_FATHERCODE", SqlDbType.VarChar,15),
                    new SqlParameter("@FUNC_LEVEL", SqlDbType.Int),
                    new SqlParameter("@FUNC_LEAF", SqlDbType.SmallInt),
                    new SqlParameter("@FUNC_STOP", SqlDbType.SmallInt),
                    new SqlParameter("@FUNC_FIELD1", SqlDbType.VarChar,20),
                    new SqlParameter("@FUNC_FIELD2", SqlDbType.VarChar,20)
            };
            parameters[0].Value = model.FUNC_CODE;
            parameters[1].Value = model.FUNC_NAME;

            if (model.FUNC_URL != null)
                parameters[2].Value = model.FUNC_URL;
            else
                parameters[2].Value = DBNull.Value;

            parameters[3].Value = model.FUNC_FATHERCODE;
            parameters[4].Value = model.FUNC_LEVEL;
            parameters[5].Value = model.FUNC_LEAF;
            parameters[6].Value = model.FUNC_STOP;

            if (model.FUNC_FIELD1 != null)
                parameters[7].Value = model.FUNC_FIELD1;
            else
                parameters[7].Value = DBNull.Value;


            if (model.FUNC_FIELD2 != null)
                parameters[8].Value = model.FUNC_FIELD2;
            else
                parameters[8].Value = DBNull.Value;


            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据

        /// </summary>
        public bool Delete(string FUNC_CODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete [FUNCTIONS] ");
            strSql.Append(" where [FUNC_CODE]=@FUNC_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@FUNC_CODE", SqlDbType.VarChar,15)};
            parameters[0].Value = FUNC_CODE;

            return SqlHelper.ExecuteSql(SqlHelper.LocalSqlServer, strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 是否存在该记录

        /// </summary>
        public bool Exists(string FUNC_CODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from [FUNCTIONS]");
            strSql.Append(" where [FUNC_CODE]= @FUNC_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@FUNC_CODE", SqlDbType.VarChar,15)};
            parameters[0].Value = FUNC_CODE;
            return SqlHelper.Exists(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体

        /// </summary>
        public Model.FUNCTIONS GetModel(string FUNC_CODE)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from [FUNCTIONS] ");
            strSql.Append(" where [FUNC_CODE]=@FUNC_CODE");
            SqlParameter[] parameters = {
                    new SqlParameter("@FUNC_CODE", SqlDbType.VarChar,15)};
            parameters[0].Value = FUNC_CODE;
            DataSet ds = SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow r = ds.Tables[0].Rows[0];
                return GetModel(r);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取数据集,建议只在多表联查时使用

        /// </summary>
        public DataSet GetDataSet(string strSql)
        {
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql);
        }

        /// <summary>
        /// 获取泛型数据列表,建议只在多表联查时使用

        /// </summary>
        public IList<Model.FUNCTIONS> GetList(string strSql)
        {
            return GetList(GetDataSet(strSql));
        }

        /// <summary>
        /// 获取数据集,在单表查询时使用
        /// </summary>
        public DataSet GetDataSet(string strWhat, string strWhere, string strOrderby)
        {
            if (string.IsNullOrEmpty(strWhat))
                strWhat = "*";
            StringBuilder strSql = new StringBuilder("select " + strWhat + " from [FUNCTIONS]");
            if (!string.IsNullOrEmpty(strWhere))
                strSql.Append(" where " + strWhere);
            if (!string.IsNullOrEmpty(strOrderby))
                strSql.Append(" order by " + strOrderby);
            return SqlHelper.Query(SqlHelper.LocalSqlServer, strSql.ToString());
        }

        /// <summary>
        /// 获取泛型数据列表,在单表查询时使用
        /// </summary>
        public IList<Model.FUNCTIONS> GetList(string strWhat, string strWhere, string strOrderby)
        {
            return GetList(GetDataSet(strWhat, strWhere, strOrderby));
        }

        /// <summary>
        /// 分页获取泛型数据列表
        /// </summary>
        public IList<Model.FUNCTIONS> GetList(int pageSize, int pageIndex, string fldSort, bool sort, string strCondition, out int pageCount, out int count)
        {
            string strSql;
            DataSet ds = SqlHelper.PageList(SqlHelper.LocalSqlServer, "[FUNCTIONS]", null, pageSize, pageIndex, fldSort, sort, strCondition, "FUNC_CODE", false, out pageCount, out count, out strSql);
            return GetList(ds);
        }
        #endregion

        /// <summary>
        /// 由一行数据得到一个实体

        /// </summary>
        private Model.FUNCTIONS GetModel(DataRow r)
        {
            Model.FUNCTIONS model = new Model.FUNCTIONS();
            model.FUNC_CODE = SqlHelper.GetString(r["FUNC_CODE"]);
            model.FUNC_NAME = SqlHelper.GetString(r["FUNC_NAME"]);
            model.FUNC_URL = SqlHelper.GetString(r["FUNC_URL"]);
            model.FUNC_FATHERCODE = SqlHelper.GetString(r["FUNC_FATHERCODE"]);
            model.FUNC_LEVEL = SqlHelper.GetInt(r["FUNC_LEVEL"]);
            model.FUNC_LEAF = SqlHelper.GetInt(r["FUNC_LEAF"]);
            model.FUNC_STOP = SqlHelper.GetInt(r["FUNC_STOP"]);
            model.FUNC_FIELD1 = SqlHelper.GetString(r["FUNC_FIELD1"]);
            model.FUNC_FIELD2 = SqlHelper.GetString(r["FUNC_FIELD2"]);
            return model;
        }

        /// <summary>
        /// 由数据集得到泛型数据列表
        /// </summary>
        private IList<Model.FUNCTIONS> GetList(DataSet ds)
        {
            List<Model.FUNCTIONS> l = new List<Model.FUNCTIONS>();
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                l.Add(GetModel(r));
            }
            return l;
        }

        /// <summary>
        /// 根据功能编号 获取单据编号
        /// </summary>
        public string GetDM(string FUNC_CODE)
        {
            SqlParameter[] parameters = 
            {
                new SqlParameter("@m", SqlDbType.VarChar,20),
                new SqlParameter("@Res", SqlDbType.VarChar,20)
            };
            parameters[0].Value = FUNC_CODE;
            parameters[1].Direction = ParameterDirection.Output;
            SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "GetDM", parameters);
            return parameters[1].Value.ToString();
        }

        /// <summary>
        /// 根据功能编号 增加最大流水号
        /// </summary>
        public void ADDLSH(string FUNC_CODE)
        {
            SqlParameter[] parameters = 
            {
                new SqlParameter("@m", SqlDbType.VarChar,20)
            };
            parameters[0].Value = FUNC_CODE;
            SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "ADDLSH", parameters);
        }

        /// <summary>
        /// 根据功能编号 增加最大流水号
        /// </summary>
        public void ADDLSH(string FUNC_CODE, SqlTransaction tran)
        {
            SqlParameter[] parameters = 
            {
                new SqlParameter("@m", SqlDbType.VarChar,20)
            };
            parameters[0].Value = FUNC_CODE;
            DBHelper.ExecuteNonQuery(tran, "ADDLSH", parameters, CommandType.StoredProcedure);
        }

        public DataSet GetAllFunctions()
        {
            return SqlHelper.RunProcedure(SqlHelper.LocalSqlServer, "SP_FUNCTIONS_GetAllFunctions", "tbl"); 

        }
    }
}
